<?php

/**
 * intract with property table
 * @author	R.A.Thilini D.R <thilinipooranee@gmail.com>
 * @date	14/02/2013
 */
class Listing_model extends CI_Model {
    /*
     * id (int)
     * external_id (varchar)
     * manager_id 
     * franchise_id 
     * customer_id
     * 
     * status
     * headline
     * address_street_no
     * address_street
     * address_type
     * address_postcode
     * price_range_min
     * bedrooms
     * bathrooms
     * carports
     * ....................
     */

    function __construct() {
        $this->load->database();
    }

    function getDefaultFranchiseID() {

        $this->db->select('id');
        $this->db->from('franchise');
        $this->db->limit(1, 0);
        $query = $this->db->get();
        $rs = $query->result();
        return $rs[0]->id;
    }

    function getFranchiseID($email) {
        $this->db->select('id');
        $this->db->from('franchise');
        $this->db->where('email', $email);
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0])) {
            return $rs[0]->id;
        } else {

            return false;
        }
    }

    function getCustomerID($email) {
        $this->db->select('id');
        $this->db->from('customers');
        $this->db->where('email', $email);
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0])) {
            return $rs[0]->id;
        } else {
            return false;
        }
    }

    function getPrevStatus($propery_id) {
        $this->db->select('status ');
        $this->db->from('property');
        $this->db->where('id', $propery_id);
        $query = $this->db->get();
        $rs = $query->result();
        $old_status = '';
        if (isset($rs[0])) {
            $old_status = $rs[0]->status;
        }
        return $old_status;
    }

    function getOne($id) {
        $this->db->cache_off();
        $thisListing = null;
        $this->db->select('property.*');
        $this->db->select('property_features.bedrooms');
        $this->db->select('property_features.bathrooms');
        $this->db->select('property_features.carparks');
        $this->db->select('property_features.land_size');
        $this->db->select('property_features.land_size_type	');
        $this->db->select('property_features.building_size');
        $this->db->select('property_features.building_size_type	');
        $this->db->select('property_features.year_built');
        $this->db->select('property_features.exterior_type');
        $this->db->select('property_features.roofing_type');

        $this->db->select('property_features.allowances_pet_friendly');
        $this->db->select('property_features.allowances_smoker');
        $this->db->select('property_features.allowances_furnished');
        $this->db->select('property_features.study');
        $this->db->select('property_features.dishwasher');
        $this->db->select('property_features.built_in_robes');
        $this->db->select('property_features.gym');
        $this->db->select('property_features.workshop');
        $this->db->select('property_features.rumpus_room');
        $this->db->select('property_features.floorboards');
        $this->db->select('property_features.broadband');
        $this->db->select('property_features.pay_tv');
        $this->db->select('property_features.ducted_heating');
        $this->db->select('property_features.ducted_cooling');
        $this->db->select('property_features.splitsystem_heating');
        $this->db->select('property_features.hydronic_heating');
        $this->db->select('property_features.splitsystem_aircon');
        $this->db->select('property_features.gas_heating');
        $this->db->select('property_features.reverse_cycle_aircon');
        $this->db->select('property_features.evaporative_cooling');
        $this->db->select('property_features.air_conditioning');
        $this->db->select('property_features.alarm_system');
        $this->db->select('property_features.vacuum_system');
        $this->db->select('property_features.pool');
        $this->db->select('property_features.spa');
        $this->db->select('property_features.tennis_court');
        $this->db->select('property_features.balcony');
        $this->db->select('property_features.deck');
        $this->db->select('property_features.courtyard');
        $this->db->select('property_features.outdoor_ent');
        $this->db->select('property_features.shed');
        $this->db->select('property_features.fully_fenced');
        $this->db->select('property_features.open_fire_place');
        $this->db->select('property_features.open_spaces');
        $this->db->select('property_features.heating_type');
        $this->db->select('property_features.hot_water_service');
        $this->db->select('property_features.inside_spa');
        $this->db->select('property_features.outside_spa');

        $this->db->select('property_features.land_crossover');
        $this->db->select('property_features.land_frontage');
        $this->db->select('property_features.land_frontage_unit');
        $this->db->select('property_features.land_depth');
        $this->db->select('property_features.land_depth_unit');
        $this->db->select('property_features.land_depthleft');
        $this->db->select('property_features.land_depthleft_unit');
        $this->db->select('property_features.land_depthright');
        $this->db->select('property_features.land_depthright_unit');
        $this->db->select('property_features.land_depthrear');
        $this->db->select('property_features.land_depthrear_unit');

        $this->db->select('property_features.other_features');
        $this->db->select('property_features.rural_features_fencing');
        $this->db->select('property_features.rural_features_annual_rainfall');
        $this->db->select('property_features.rural_features_soiltypes');
        $this->db->select('property_features.rural_features_improvements');
        $this->db->select('property_features.rural_features_councilrates');
        $this->db->select('property_features.rural_features_irrigation');
        $this->db->select('property_features.rural_features_carryingcapacity');
        $this->db->select('property_features.rural_features_services');

        $this->db->select('property_features.eco_friendly_solar_panels');
        $this->db->select('property_features.eco_friendly_solar_hot_water');
        $this->db->select('property_features.eco_friendly_water_tank');
        $this->db->select('property_features.eco_friendly_grey_water_system');
        $this->db->select('property_features.idealfor_first_home_buyer');
        $this->db->select('property_features.idealfor_investors');
        $this->db->select('property_features.idealfor_downsizing');
        $this->db->select('property_features.idealfor_couples');
        $this->db->select('property_features.idealfor_students');
        $this->db->select('property_features.idealfor_irg_families');
        $this->db->select('property_features.idealfor_reitrees');
        $this->db->select('property_features.views_city');
        $this->db->select('property_features.views_water');
        $this->db->select('property_features.views_valley');
        $this->db->select('property_features.views_mountain');
        $this->db->select('property_features.views_ocean');
        $this->db->select('property_features.property_extent');

        $this->db->select('property_features.car_spaces');
        $this->db->select('property_features.parking_comments');



        $this->db->select('customers.fName');
        $this->db->select('customers.lName');
        $this->db->select('customers.email');

        $this->db->select('franchise.name AS agentname');
        $this->db->select('franchise.email AS agentemail');
        $this->db->select('franchise.phone AS agentphone');
        $this->db->select('franchise.fax AS agentfax');
        $this->db->select('franchise.location_addr_street1 AS agentlocation_addr_street1');
        $this->db->select('franchise.location_addr_street2 AS agentlocation_addr_street2');
        $this->db->select('franchise.location_addr_suburb AS agentlocation_addr_suburb');
        $this->db->select('franchise.location_addr_state AS agentlocation_addr_state');
        $this->db->select('franchise.location_addr_pcode AS agentlocation_addr_pcode');

        $this->db->select('users_data.fName AS managerfname');
        $this->db->select('users_data.lName AS managerlname');
        $this->db->select('users_data.phone AS managerphone');
        $this->db->select('users.email AS manageremail');


        $this->db->from('property');
        $this->db->join('property_features', 'property.id = property_features.property_id', 'left');
        $this->db->join('customers', 'property.customer_id = customers.id', 'left');
        $this->db->join('franchise', 'property.franchise_id = franchise.id', 'left');
        $this->db->join('users_data', 'property.manager_id = users_data.user_id', 'left');
        $this->db->join('users', 'property.manager_id = users.id', 'left');

        $this->db->where('property.id', $id);
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0])) {
            $thisListing = $rs[0];

            //get details from subtables

            $this->db->select('*');
            $this->db->from('property_image');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_images = $query->result();

            $this->db->select('*');
            $this->db->from('property_document');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_documents = $query->result();


            $this->db->select('*');
            $this->db->from('property_highlight');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_highlights = $query->result();

            $this->db->select('*');
            $this->db->from('property_inspection_time');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_inspection_times = $query->result();


            $this->db->select('*');
            $this->db->from('property_status_history');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_status_history = $query->result();

            $this->db->select('*');
            $this->db->from('property_view');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_views = $query->result();

            $this->db->select('*');
            $this->db->from('property_enquiry');
            $this->db->where('property_id', $id);
            $query = $this->db->get();
            $thisListing->property_enquiry = $query->result();
        }
        return $thisListing;
    }

    function getListingID($id) {
        $this->db->select('id');
        $this->db->from('property');
        $this->db->where('external_id', $id);
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0])) {
            return $rs[0]->id;
        } else {
            return 0;
        }
    }

    function count_listing($status, $search = array(), $arroundWhere = array()) {
        $this->db->cache_on();
        $this->db->select('COUNT(*) as cnt');
        $this->db->from('property');
        $this->db->join('property_features', 'property.id = property_features.property_id', 'left');
       
        if (count($arroundWhere) > 0) {
            $this->db->where_in('property.id', $arroundWhere);
        }

        if ($status == 'listings') {
            $this->db->where_in('property.status', array(1, 2, 3, 4, 5));
        }
        if ($status == 'rentals') {
            $this->db->where_in('property.status', 11);
        }if ($status == 'sold') {
            $this->db->where_in('property.status', 8);
        }if ($status == 'leased') {
            $this->db->where_in('property.status', 12);
        }

        if (count($search) > 0) {
            foreach ($search as $key => $sval) {
                if ($key == 'search_minprice') {
                    $val = str_replace('$', '', $sval);
                    $this->db->where('property.price_min >=', (int) $val * 1000);
                } else if ($key == 'search_maxprice') {
                    $val = str_replace('$', '', $sval);
                    $this->db->where('property.price_max <=', (int) $val * 1000);
                } else if ($key == 'search_bathroom') {
                    $this->db->where('property_features.bathrooms', (int) $sval);
                } else if ($key == 'search_bedroom') {
                    $this->db->where('property_features.bedrooms', (int) $sval);
                } else if ($key == 'search_propertytype') {
                    $this->db->where('property.property_type', $sval);
                } else if ($key == 'search_status') {
                    $this->db->where('property.status', $sval);
                } else if ($key == 'suburbsSearch') {
                    $subPostArray = explode(",", $sval);
                    if (isset($subPostArray[1])) {
                        $suburbArray = explode(" ", $subPostArray[0]);
                        $this->db->like('LOWER(property.address_suburb)', trim(strtolower($suburbArray[0])));
                        $this->db->like('property.address_postcode', trim($subPostArray[1]));
                    } else if (isset($subPostArray[0])) {
                        $suburbArray = explode(" ", $subPostArray[0]);
                        $this->db->like('LOWER(property.address_suburb)', trim(strtolower($suburbArray[0])));
                    }
                }
            }
            //add search array
            //$this->db->like('listing_category', $search);
        }


        $query = $this->db->get();
         $rs = $query->result();
         if (isset($rs[0])) {
            return $rs[0]->cnt;
        } else {
            return 0;
        }
    }

    function getCurrent($status, $search = array(), $arroundWhere = array(), $limit = '', $start = '', $sortby = 'created_date', $order = 'asc') {

        $this->db->cache_on();

        $this->db->select('property.property_type');
        $this->db->select('property.id');
        $this->db->select('property.external_id');
        $this->db->select('property.status');
        $this->db->select('property.activity_type');

        //price       
        $this->db->select('property.display_price');
        $this->db->select('property.price_min');
        $this->db->select('property.price_max');
        //address

        $this->db->select('property.address_display');
        $this->db->select('property.address_street_no');
        $this->db->select('property.address_street');
        $this->db->select('property.address_street_type');
        $this->db->select('property.address_suburb');
        $this->db->select('property.address_postcode');
        $this->db->select('property.address_state');
        $this->db->select('property.address_country');

        $this->db->select("CONCAT(`address_street_no`, `address_street`, `address_suburb`, `address_postcode`, `address_state`) AS location", false);


        //basic
        $this->db->select('property.headline');
        $this->db->select('property.description');

        //features
        $this->db->select('property_features.bedrooms');
        $this->db->select('property_features.bathrooms');
        $this->db->select('property_features.carparks');

        $this->db->select('property_features.land_size');
        $this->db->select('property_features.land_size_type');

       

        $this->db->from('property');
        $this->db->join('property_features', 'property.id = property_features.property_id', 'left');
       

        if (count($arroundWhere) > 0) {
            $this->db->where_in('property.id', $arroundWhere);
        }

        if ($status == 'listings') {
            $this->db->where_in('property.status', array(1, 2, 3, 4, 5));
        }
        if ($status == 'rentals') {
            $this->db->where_in('property.status', 11);
        }if ($status == 'sold') {
            $this->db->where_in('property.status', 8);
        }if ($status == 'leased') {
            $this->db->where_in('property.status', 12);
        }

        if (count($search) > 0) {
            foreach ($search as $key => $sval) {
                if ($key == 'search_minprice') {
                    $val = str_replace('$', '', $sval);
                    $this->db->where('property.price_min >=', (int) $val * 1000);
                } else if ($key == 'search_maxprice') {
                    $val = str_replace('$', '', $sval);
                    $this->db->where('property.price_max <=', (int) $val * 1000);
                } else if ($key == 'search_bathroom') {
                    $this->db->where('property_features.bathrooms', (int) $sval);
                } else if ($key == 'search_bedroom') {
                    $this->db->where('property_features.bedrooms', (int) $sval);
                } else if ($key == 'search_propertytype') {
                    $this->db->where('property.property_type', $sval);
                } else if ($key == 'search_status') {
                    $this->db->where('property.status', $sval);
                } else if ($key == 'franchise_id') {
                    $this->db->where('property.franchise_id', $sval);
                } else if ($key == 'suburbsSearch') {
                    $subPostArray = explode(",", $sval);
                    if (isset($subPostArray[1])) {
                        $suburbArray = explode(" ", $subPostArray[0]);
                        $this->db->like('LOWER(property.address_suburb)', trim(strtolower($suburbArray[0])));
                        $this->db->like('property.address_postcode', trim($subPostArray[1]));
                    } else if (isset($subPostArray[0])) {
                        $suburbArray = explode(" ", $subPostArray[0]);
                        $this->db->like('LOWER(property.address_suburb)', trim(strtolower($suburbArray[0])));
                    }
                }
            }
            //add search array
            //$this->db->like('listing_category', $search);
        }


        $this->db->limit($limit, $start);

        $this->db->order_by($sortby, $order);


        $query = $this->db->get();
        return $query->result();
    }

    function getImage($id) {
        $this->db->select('*');
        $this->db->from('property_image');
        $this->db->where('property_id', $id);
        $this->db->limit(1, 0);
        $this->db->order_by('display_order', 'ASC');
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0]))
            return $rs[0];
        else
            return false;
    }

    function getVendeor($id) {
        $this->db->select('*');
        $this->db->from('customers');
        $this->db->where('id', $id);
        $this->db->limit(1, 0);
        $query = $this->db->get();
        $rs = $query->result();
        if (isset($rs[0]))
            return $rs[0];
        else
            return false;
    }

    function getVendeors() {
        $this->db->select('*');
        $this->db->from('customers');

        $query = $this->db->get();
        $rs = $query->result();

        return $rs;
    }

    function enquiry_insert($data) {
        $this->db->insert('property_enquiry', $data);
        return $this->db->insert_id();
    }

    function around_suburbs($suburb_id) {
        $this->load->model('au_suburb_geo_model', 'suburb');
        $suburd_around = array();
        $return = array();
        $au_suburb_geo = $this->suburb->getAuSuburbById($suburb_id);
        //var_dump($au_suburb_geo);die;
        $state = $au_suburb_geo->State1;
        $locality = $au_suburb_geo->Locality;
        $lat = $au_suburb_geo->Lat;
        $long = $au_suburb_geo->Long;
        $pcode = $au_suburb_geo->Pcode;
        //var_dump($state);die;
        if ($lat != null && $long != null) {
            $suburd_around = $this->suburb->getSuburbAround($lat, $long);
        }

        $where = 'SELECT id FROM property';
        if (count($suburd_around > 0)) {
            $where = 'SELECT id FROM property WHERE';

            foreach ($suburd_around as $sa) {

                $paddress_suburb = $sa['Locality'];
                $paddress_postcode = $sa['Pcode'];
                $wherephrase = " ( address_suburb LIKE '%" . $paddress_suburb . "%' OR address_postcode ='" . $paddress_postcode . "' ) ";
                if ($where == 'SELECT id FROM property WHERE') {
                    $where .= $wherephrase;
                } else {
                    $where .=' OR ' . $wherephrase;
                }
            }

            $resultStart = $this->db->query($where);
            $rs = $resultStart->result();
            foreach ($rs as $one) {
                $return[$one->id] = $one->id;
            }
        }
        return $return;
    }
    
    public function deleteCache(){
        $this->db->cache_delete_all();
    }

    ///getBannerSlide top 5
    function getBannerSlide() {
        $this->db->select("property_image.image_url,property_image.property_id");
        $this->db->from("property_image");
        $this->db->join("property", "property.id = property_image.property_id");
        $this->db->where("property.activity_type", "selling");
        $this->db->where("property.status", "2");
        $this->db->group_by("property_image.property_id");
        $this->db->order_by("property.id", "DESC");
        $this->db->limit(5);
        $query = $this->db->get();
        return $query->result();
    }

}

?>